#####################################################################################
#
# 使用 pymssql 动态查询 SQL Server
#
#####################################################################################

import pymssql

class MSSQL:
    def __init__(self, server, username, password, database):
        self.connection = pymssql.connect(server, username, password, database)

    def __enter__(self):
        print('__enter__')
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        print('__exit__')
        self.connection.commit()
        self.connection.close()

    def execute_query(self, query, params = None):
        with self.connection.cursor() as cursor:  # 获取游标(cursor)
            cursor.execute(query, params)  # 执行查询语句
            return cursor.fetchall()  # 获取结果集
    
    def execute_non_query(self, query, params = None):
        with self.connection.cursor() as cursor:  # 获取游标(cursor)
            cursor.execute(query, params)  # 执行增删改语句
            self.connection.commit()  # 提交事务
            return cursor.rowcount  # 返回受影响的行数
    

if __name__ == '__main__':
    with MSSQL('msdbt', 'wluser', 'wlpwd.123', 'wldbtest') as mssql:
        count = mssql.execute_query('SELECT COUNT(*) AS count FROM persons')
        print(count)

        rows = mssql.execute_query('SELECT * FROM persons ORDER BY id')
        for row in rows:
            print(row)
